import sqlite3
import threading
import datetime
from typing import Tuple, Dict

class UserManager:
    _instance = None
    _lock = threading.Lock()
    
    def __new__(cls, db_file="./database/MyoEcho.db"):
        if cls._instance is None:
            with cls._lock:
                # 双重检查锁定模式
                if cls._instance is None:
                    cls._instance = super(UserManager, cls).__new__(cls)
                    cls._instance._initialized = False
        return cls._instance

    def __init__(self, db_file="./database/MyoEcho.db"):
        if self._initialized:
            return
        self._initialized = True
        self.db_file = db_file
        # 初始化线程本地存储
        self.local = threading.local()
        # 确保数据库和表存在
        self._init_database()
        # 权限映射
        self.permission_map = {
            1:"管理员",
            2:"普通用户",
            "管理员":1,
            "普通用户":2
        }

    def _init_database(self):
        # 初始化数据库和表结构
        conn = sqlite3.connect(self.db_file)
        cursor = conn.cursor()
        # 创建用户表（如果不存在），添加salt字段
        cursor.execute('''     
            CREATE TABLE IF NOT EXISTS [User](
                [userID] INTEGER PRIMARY KEY NOT NULL UNIQUE, 
                [userName] VARCHAR(1024) NOT NULL UNIQUE, 
                [pwd] VARCHAR(64) NOT NULL, 
                [remark] VARCHAR(1024), 
                [permissions] TINYINT NOT NULL DEFAULT 2, 
                [createTime] TEXT NOT NULL);
        ''')
        conn.commit()
        conn.close()

    def _get_connection(self):
        """获取连接"""
        # 为每个线程获取或创建数据库连接
        if not hasattr(self.local, 'conn'):
            self.local.conn = sqlite3.connect(self.db_file, 10)
            self.local.cursor = self.local.conn.cursor()
        return self.local.conn, self.local.cursor
    
    def authenticate(self, user_name:str, password:str) -> Tuple[bool, str, str]:
        """验证用户"""
        _, cursor = self._get_connection()
        # 获取用户的密码哈希
        cursor.execute("SELECT permissions, remark FROM User WHERE userName = ? and pwd = ?;", (user_name, password))
        result = cursor.fetchone()
        if result:
            permissions, remark = result
            return True, permissions, remark
        return False, None, None


    
    def get_user_data(self, user_name:str = None) -> list[Dict[str, str|int]]:
        """获取用户信息"""
        _, cursor = self._get_connection()
        if user_name is None:
            cursor.execute("SELECT userName, remark, permissions, createTime FROM User;")
        else:
            cursor.execute("SELECT userName, remark, permissions, createTime FROM User WHERE userName = ?;",(user_name,))
        cursor_result = cursor.fetchall()
        return [
            {
                "userName": userName,
                "remark": remark,
                "permission": permissions,
                "createTime": createTime
            }
            for userName, remark, permissions, createTime in cursor_result
        ]

    
    def add_user(self, user_name:str, password:str, remark:str, permissions:int = 2):
        """添加用户"""
        # 添加新用户
        conn, cursor = self._get_connection()
        try:
            # 获取当前时间
            cur_date:str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            # 存储哈希密码
            cursor.execute("INSERT INTO User (userName, pwd, remark, permissions, createTime) VALUES (?, ?, ?, ?, ?)", 
                               (user_name, password, remark, permissions, cur_date))
            row:int = cursor.rowcount
            if row > 0:
                conn.commit()
                return True, "用户创建成功"
            else:
                conn.rollback()
                return False, "用户名已存在"
        except sqlite3.IntegrityError as e:
            print("用户名已存在")
            return False, "用户名已存在"

    
    def modify_pwd(self, user_name, new_password):
        """修改密码"""
        conn, cursor = self._get_connection()
        cursor.execute("SELECT * FROM User WHERE userName=?;",(user_name,))
        res:list[any] = cursor.fetchall()
        if res:
            cursor.execute("UPDATE User SET pwd=? WHERE userName=?;", (new_password, user_name))
            conn.commit()
            return True
        else:
            return False


    
    def delete_user(self, user_name:str):
        """删除用户"""
        conn, cursor = self._get_connection()
        # 1. 先判断用户是否存在
        cursor.execute("SELECT * FROM User WHERE userName=?;",(user_name,))
        if cursor.fetchone():
            # 2. 删除用户
            cursor.execute("DELETE FROM User WHERE userName=?;",(user_name,))
            if cursor.rowcount > 0:
                conn.commit()
                return True
            else:
                conn.rollback()
                return False
        return False

    def __del__(self):
        # 关闭线程本地存储中的数据库连接
        if hasattr(self.local, 'conn'):
            self.local.conn.close()

if __name__ == "__main__":
    um = UserManager()
    um2 = UserManager()
    print(f"um is um2: {um is um2}")  # 验证是否为同一实例
    
    um.add_user("admin", "admin","这是测试账号",1)
    um.get_user_data()
    ok, permissions, remark = um.authenticate("admin","8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918")
    print("认证信息：", ok, permissions, remark)
    print("修改密码测试")
    ok = um.modify_pwd("admin", "123456")
    print("修改密码结果：",ok)
    print("再次验证")
    ok, permissions, remark = um.authenticate("admin","8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92")
    print("认证信息：",ok, permissions, remark)